package com.sl.au.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Chance;
import com.sl.au.entity.ExpectedOrder;


@Transactional(readOnly = true)
public interface ExpectedOrderRepository extends JpaRepository<ExpectedOrder, String> {
	
	@Query(" select a from ExpectedOrder a where a.id=?1")
	Page<ExpectedOrder> findExpectedOrder(String key, Pageable pageable);
	
	
	@Query(" select a from ExpectedOrder a where a.createTime >=  convert(?1,'yyyy-mm-dd') and a.createTime <  convert(?2,'yyyy-mm-dd')")
	Page<ExpectedOrder> findExpectedOrderByTiem(String begin,String end, Pageable pageable);
	
	@Query(" select count(*) from ExpectedOrder a where a.id=?1")
	int getExpectedOrderCount(String key);
	
	@Query(" select a from ExpectedOrder a ")
	Page<ExpectedOrder> findExpectedOrder(Pageable pageable);
	
	@Query(" select count(*) from ExpectedOrder a")
	int getExpectedOrderCount();

	/**
	 * 导出-所有
	 * @param begin
	 * @param end
	 * @param pageable
	 * @return
	 */
	@Query(" select a from ExpectedOrder a where a.createTime>=?1 and a.createTime <?2 and a.chance.isSubmit = 1 and a.chance.isSucceed is null ")
	Page<ExpectedOrder> findAllByTime(Date begin, Date end, Pageable pageable);
	
	/**
	 * 导出-本部门
	 * @param begin
	 * @param end
	 * @param pageable
	 * @return
	 */
	@Query(" select a from ExpectedOrder a where a.createTime>=?1 and a.createTime <?2 and a.chance.sourceSuperior=?3  and a.chance.isSubmit = 1 and a.chance.isSucceed is null ")
	Page<ExpectedOrder> findSuperiorByTime(Date begin, Date end, String superior,Pageable pageable);
	
	/**
	 * 导出-本片区
	 * @param begin
	 * @param end
	 * @param pageable
	 * @return
	 */
	@Query(" select a from ExpectedOrder a where a.createTime>=?1 and a.createTime <?2 and a.chance.sourceArea=?3  and a.chance.isSubmit = 1 and a.chance.isSucceed is null ")
	Page<ExpectedOrder> finAreaByTime(Date begin, Date end, String area,Pageable pageable);
	
	/**
	 * 导出-本片区
	 * @param begin
	 * @param end
	 * @param pageable
	 * @return
	 */
	@Query(" select a from ExpectedOrder a where a.createTime>=?1 and a.createTime <?2 and a.user.id=?3  and a.chance.isSubmit = 1 and a.chance.isSucceed is null ")
	Page<ExpectedOrder> findSelfByTime(Date begin, Date end, String userId,Pageable pageable);
	
}
